SELECT  
        receivepayment.id,
        receivepayment.code,
        receivepayment.date,
        DATE_FORMAT(receivepayment.date, '%m/%d/%Y') as date_td, 
        receivepayment.reference_number,        
        receivepayment.notes,
        CONCAT('[', JSON_OBJECT('id', CONCAT(receivepayment.customer_id), 'displayText',  CONCAT(customer.name)), ']') AS customer_id,
        CONCAT('[', JSON_OBJECT('id', CONCAT(receivepayment.accounts_id), 'displayText',  CONCAT(accounts.name)), ']') AS accounts_id,
        CONCAT(receivepayment.amount) as amount,
        CONCAT('$',FORMAT(receivepayment.amount,2)) as amount_dt,
        customer.name as customer_name,
        customer.avatar,
        customer.avatarColor,
        customer.avatarColorLetter,
        customer.avatarLetter,
        accounts.name as accounts_name,
        accounts.avatar as accounts_avatar,
        accounts.avatarColor as accounts_avatar_color,
        accounts.avatarColorLetter as accounts_avatar_color_letter,
        accounts.avatarLetter as accounts_avatar_letter,
        CONCAT(COUNT(payment_vs_sale.sale_id)) as paid_invoices,
        GROUP_CONCAT(sale.code SEPARATOR ', ') as sales_code, 
        CONCAT(
            '[',
                
                '{',
                    '"data":[',
                                  GROUP_CONCAT(
                                        DISTINCT(
                                        JSON_OBJECT(
                                            'sale_id',CONCAT(sale.id),
                                            'credit_amount',  CONCAT(sale.credit_amount) ,       
                                            'spent_credit',  CONCAT(sale.spent_credit)            
                                                )

                                        ) SEPARATOR ','),
                            ']',
                '}',  
            
            ']'
            ) AS sale_used,


    
        CONCAT(
                '[',
                    
                    '{',
                        '"tableName":"payment_vs_sale"', 
                        ',"data":[',
                                    GROUP_CONCAT(
                                            DISTINCT(
                                            JSON_OBJECT(
                                                'id',CONCAT(payment_vs_sale.id),  
                                                'sale_id', CONCAT('[', JSON_OBJECT('id',   CONCAT(sale.id), 'displayText', CONCAT(sale.code, ' - ',DATE_FORMAT(sale.sale_date, '%m/%d/%Y') ) ),']'),              
                                                'po_total_amount', CONCAT(payment_vs_sale.po_total_amount),
                                                'due_amount', CONCAT(payment_vs_sale.due_amount),  
                                                'sent_amount',  CONCAT(payment_vs_sale.sent_amount)                  
                                                )

                                            ) SEPARATOR ','),
                                ']',
                    '}'






                
                ']'
            ) AS multiTables


        FROM receivepayment 
        INNER JOIN customer on customer.id = receivepayment.customer_id 
        INNER JOIN accounts on accounts.id  = receivepayment.accounts_id
        INNER  JOIN payment_vs_sale on payment_vs_sale.receivepaymentId = receivepayment.id        
        INNER  JOIN sale on sale.id = payment_vs_sale.sale_id
        LEFT  JOIN receivepayment_vs_credit on receivepayment_vs_credit.receivepaymentId = receivepayment.id
        WHERE receivepayment.deleted = '0' AND receivepayment.id = '3579' ;